package locacao.integracao.dao.pstPostgreSQL;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;

import locacao.integracao.dao.ClienteDAO;
import locacao.negocio.to.ClienteTO;

public class ClientePgsqlDAO implements ClienteDAO {

    private ArrayList<ClienteTO> clientes;

    public ClientePgsqlDAO(){
	   setClientes(new ArrayList<ClienteTO>());
    }


    public void addOrUpdateCliente(ClienteTO cliente) {
    	Connection conn = null;
    	CallableStatement spManterCliente = null;

    	try {        	
          String sql = "{call locacao.manter_cliente(?,?,?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spManterCliente = conn.prepareCall(sql);
          
          spManterCliente.setInt(1, cliente.getIdCliente());
          spManterCliente.setInt(2, cliente.getIdPessoa());
          spManterCliente.setString(3, cliente.getCnh());
          
          spManterCliente.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spManterCliente != null)
            	spManterCliente.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }

    /**
     *

    /**
     * @param cliente
     */
    public void excludeCliente( ClienteTO cliente )  {
    	Connection conn = null;
    	CallableStatement spDeleteCliente = null;

    	try {        	
          String sql = "{call locacao.delete_cliente(?)}";
          conn = ConnectionRentACar.getInstance().getConnection();
          
          spDeleteCliente = conn.prepareCall(sql);
          
          spDeleteCliente.setInt(1, cliente.getIdCliente());         
          
          spDeleteCliente.execute();
        } catch (Exception e) {
          System.out.println(e.getMessage());
        } finally {
          try {
            if (spDeleteCliente != null)
            	spDeleteCliente.close();

          } catch (SQLException e) {
            System.out.println(e.getMessage());
          }
        }
    }


  
    /**
     * @return
     */
    public String[] findClienteById(Integer id){
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 String[] info = null;

    	 String sql = "SELECT  idcliente, idpessoa, cnh ";
    	 sql += " FROM locacao.cliente WHERE idcliente =  " + id;

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {    			 
    			 while (rSet.next()) {
    				 
    				info = new String[clientes.size()];
    	    		   info[0] = rSet.getInt("idcliente") 
    	       		   + " - " + rSet.getInt("idpessoa") 
    	       		   + " - " + rSet.getString("cnh");	    		   
    			 }    			 
    		 }
			 return info;
			 
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    		 return null;
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    }

    
    /**
     * @return
     */
    public String[] getInfoClientes()
    {
        ClienteTO cliente;
        clientes = getClientes();
        String[] info = new String[clientes.size()];
        Iterator iteratorClientes;
        iteratorClientes = clientes.iterator();
        int i=0;
        while ( iteratorClientes.hasNext() )
        {
            cliente = (ClienteTO) iteratorClientes.next();
            info[i] = cliente.getIdCliente() 
            		+ " - " + cliente.getIdPessoa()
            		+ " - " + cliente.getCnh();
            i++;
        }
        return info;
    }


    /**
     * @return
     */
    public ArrayList<ClienteTO> getClientes() {
    	 Connection conn = null;
    	 Statement stmt = null;
    	 ResultSet rSet = null;
    	 ClienteTO cliente = null;

    	 String sql = "SELECT  idcliente, idPessoa, cnh ";
    	 sql += " FROM locacao.cliente ORDER BY idcliente";

    	 try {
    		 conn = ConnectionRentACar.getInstance().getConnection();
    		 stmt = conn.createStatement();
    		 rSet = stmt.executeQuery(sql);

    		 if (rSet != null) {
    			 clientes = new ArrayList();
    			 while (rSet.next()) {
    				 
    				 cliente = new ClienteTO();
    				 
    				 cliente.setIdCliente(rSet.getInt("idcliente"));
    				 cliente.setIdPessoa(rSet.getInt("idPessoa"));
    				 cliente.setCnh(rSet.getString("cnh"));
    				 
    				 clientes.add(cliente);
    			 }
    		 }
    	 } catch (Exception e) {
    		 System.out.println(e.getMessage());
    	 } finally {
    		 try {
    			 if (rSet != null)
    				 rSet.close();
    			 if (stmt != null)
    				 stmt.close();
    		 } catch (SQLException e) {
    			 System.out.println(e.getMessage());
    		 }
    	 }
    	 return clientes;
    }

    private void setClientes(ArrayList<ClienteTO> clientes) {
    	this.clientes = clientes;
    }
    
}
